"""
Case Type   : GSC功能模块
Case Name   : 线程池模式rename、或者删除数据库后，数据库缓存信息是否正确
Create At   : 2024/10/26
Owner       : @lonely-dance
Description :
    1.配置数据库参数
    2.重启数据库生效
    3.创建数据库和表
    4.连接新建的数据库，查看指定库缓存信息
    5.修改数据库名称
    6.查看指定库缓存信息
    7.新建数据库
    8.查看指定库缓存信息
    9.删除数据库
    10.查看指定库缓存信息
    11.清理环境
Expect      :
    1.修改参数成功
    2.重启成功
    3.创建成功
    4.查寻成功
    5.修改成功
    6.查询成功
    7.创建成功
    8.查询成功
    9.删除成功
    10.查询成功
    11.成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.db_name1 = 'db_gsc0059'
        self.db_name2 = 'db_gsc0059_new'
        self.tb_name = 'tb_gsc0059'
        self.com = Common('PrimaryDbUser')
        self.constant = Constant()
        self.p_node = Node('PrimaryDbUser')
        self.init_para1 = None
        self.init_para2 = None

    def test_GSC(self):
        text = '----step1:配置数据库参数;----'
        self.log.info(text)
        status_result = self.pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + text)
        text = '----查看初始配置值;----'
        self.log.info(text)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        self.init_para2 = self.com.show_param('enable_thread_pool')
        self.log.info(self.init_para1)
        self.log.info(self.init_para2)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache=on")
        self.log.info(result)
        self.assertTrue(result, '执行失败:' + text)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_thread_pool=on")
        self.assertTrue(result, '执行失败:' + text)

        text = '----step2:重启数据库生效; expect:重启成功----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        text = f'----step3:创建数据库和表; expect:创建成功----'
        self.log.info(text)
        create_db = f'drop database if exists {self.db_name1};' \
                    f'create database {self.db_name1};'
        create_result = self.pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + text)

        create_tb = f"""
            create table {self.tb_name} (id int ,name text);select * from {self.tb_name};
        """
        self.log.info(create_tb)
        create_result = self.pri_sh.execut_db_sql(create_tb,
                                                  dbname=f'{self.db_name1}')
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + text)

        text = '----step4:查询缓存; expect:操作成功----'
        self.log.info(text)
        select_sql = f"""
            select count(*) from gs_gsc_catalog_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
            select count(*) from gs_gsc_table_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
            select * from gs_gsc_memory_detail where db_id in ((select cast((select oid from pg_database where datname in  ('{self.db_name2}','{self.db_name1}')) as text)));
            select * from gs_gsc_dbstat_info() where database_name in ('{self.db_name2}','{self.db_name1}');
        """
        select_result = self.pri_sh.execut_db_sql(select_sql,
                                                  dbname=f'{self.db_name1}')
        self.log.info(select_result)
        self.assertIn(f'{self.db_name1}', select_result, '执行失败:' + text)
        self.assertTrue(select_result.count('(1 row)') == 4, '执行失败:' + text)

        text = '----step5:修改数据库名称; expect:连接成功----'
        self.log.info(text)
        cmd = f"""
            alter database {self.db_name1} rename to {self.db_name2};
        """
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.assertIn(self.constant.ALTER_DATABASE_SUCCESS_MSG, res, '执行失败:' + text)

        text = '----step6:查询缓存; expect:操作成功----'
        self.log.info(text)
        select_sql = f"""
                    select count(*) from gs_gsc_catalog_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
                    select count(*) from gs_gsc_table_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
                    select * from gs_gsc_memory_detail where db_id in ((select cast((select oid from pg_database where datname in  ('{self.db_name2}','{self.db_name1}')) as text)));
                    select * from gs_gsc_dbstat_info() where database_name in ('{self.db_name2}','{self.db_name1}');
                """
        select_result = self.pri_sh.execut_db_sql(select_sql,
                                                  dbname=f'{self.db_name2}')
        self.log.info(select_result)
        self.assertIn(f'{self.db_name2}', select_result, '执行失败:' + text)
        self.assertTrue(select_result.count('(1 row)') == 4, '执行失败:' + text)

        text = '----step7:再次新建数据库db_name1,并连接; expect:操作成功----'
        self.log.info(text)
        cmd = f"""
            source {macro.DB_ENV_PATH}
            gsql -d {self.db_name2} -p {self.p_node.db_port} -c "create database {self.db_name1};"
            gsql -d {self.db_name1} -p {self.p_node.db_port} -c "\d"
            gsql -d {self.db_name2} -p {self.p_node.db_port} -c "\d"
        """
        self.log.info(cmd)
        res = self.p_node.sh(cmd).result()
        self.log.info(res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res, '执行失败:' + text)
        self.assertNotIn('ERROR', res, '执行失败:' + text)

        text = '----step8:查询缓存; expect:操作成功----'
        self.log.info(text)
        cmd = f"""
            select count(*) from gs_gsc_catalog_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
            select count(*) from gs_gsc_table_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
            select * from gs_gsc_memory_detail where db_id in ((select cast((select oid from pg_database where datname in  ('{self.db_name1}')) as text)));
            select * from gs_gsc_memory_detail where db_id in ((select cast((select oid from pg_database where datname in  ('{self.db_name2}')) as text)));
            select * from gs_gsc_dbstat_info() where database_name in ('{self.db_name2}','{self.db_name1}');
        """
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(f'{self.db_name2}', res, '执行失败:' + text)
        self.assertTrue(select_result.count('(1 row)') == 4 and '(2 rows)' in res, '执行失败:' + text)

        text = '----step9:删除数据库; expect:删除成功----'
        self.log.info(text)
        cmd = f"""
            drop database if exists {self.db_name1};
            drop database if exists {self.db_name2};
        """
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertTrue(res.count(self.constant.DROP_DATABASE_SUCCESS) == 2, '执行失败:' + text)

        text = '----step10:查询缓存; expect:操作成功----'
        self.log.info(text)
        cmd = f"""
                    select count(*) from gs_gsc_catalog_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
                    select count(*) from gs_gsc_table_detail() where database_name in ('{self.db_name2}','{self.db_name1}');
                    select * from gs_gsc_memory_detail where db_id in ((select cast((select oid from pg_database where datname in  ('{self.db_name1}')) as text)));
                    select * from gs_gsc_memory_detail where db_id in ((select cast((select oid from pg_database where datname in  ('{self.db_name2}')) as text)));
                    select * from gs_gsc_dbstat_info() where database_name in ('{self.db_name2}','{self.db_name1}');
                """
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertTrue(res.count('(1 row)') == 2 and res.count('(0 rows)') == 3, '执行失败:' + text)

    def tearDown(self):
        text = '----step11:清理环境; expect:成功----'
        self.log.info(text)
        cmd = f"""
            drop database if exists {self.db_name1};
            drop database if exists {self.db_name2};
        """
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertTrue(res.count(self.constant.DROP_DATABASE_SUCCESS) == 2, '执行失败:' + text)

        text = '----恢复数据库参数----'
        self.log.info(text)
        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache={self.init_para1}")
        self.assertTrue(result, '执行失败:' + text)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_thread_pool={self.init_para2}")
        self.assertTrue(result, '执行失败:' + text)

        text = '----重启数据库----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        self.log.info(f'----{os.path.basename(__file__)} end----')
